package com.hongdee.scm.monthlybalance.service.impl;

import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.activity.entity.TCrmCarActivity;
import com.hongdee.scm.common.service.ICommonService;
import com.hongdee.scm.common.service.impl.Common;
import com.hongdee.scm.inputinvoice.entity.TScmCostDifference;
import com.hongdee.scm.inventory.entity.TScmInventory;
import com.hongdee.scm.inventory.entity.TScmInventoryDetail;
import com.hongdee.scm.monthlybalance.dao.ITScmMonthlyBalanceDao;
import com.hongdee.scm.monthlybalance.entity.TScmDiffCar;
import com.hongdee.scm.monthlybalance.entity.TScmDifferCost;
import com.hongdee.scm.monthlybalance.entity.TScmMonthlyBalance;
import com.hongdee.scm.monthlybalance.service.ITScmMonthlyBalanceService;
import jdk.nashorn.internal.parser.Token;
import org.apache.poi.hssf.usermodel.*;
import org.hibernate.mapping.TableOwner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Mr. Xu on 2017/6/26.
 */
@Service
public class TScmMonthlyBalanceServiceImpl implements ITScmMonthlyBalanceService {
    @Autowired
    private ITScmMonthlyBalanceDao itScmMonthlyBalanceDao;
    /**
     * 注入 hibernateDao
     */
    @Autowired
    private IHibernateDao ihibernateDao;
    private String tabName = "T_Scm_MonthlyBalance";
    @Autowired
    private ICommonService iCommonService;

    /**
     * 申请月结按钮
     * @param uid
     * @param date
     * @throws Exception
     */
    @Override
    public void applyMonthlyBalance(String uid,String date) throws  Exception{

        //当前月
        List now = this.itScmMonthlyBalanceDao.list("select * from t_scm_monthlybalance where monthly_balance_month = ? and company_id=?", date,TokenUtil.getCompanyId(uid));
        if(now.size()>0){
            throw new Exception("该月已存在");
        }
        //之前月
        List next = this.itScmMonthlyBalanceDao.list("select * from t_scm_monthlybalance where monthly_balance_month >= ? and company_id=? order by monthly_balance_month limit 1", date,TokenUtil.getCompanyId(uid));
        if(next.size()>0){
            throw new Exception("该月不能月结");
        }
        //之后月
        List last = this.itScmMonthlyBalanceDao.list("select * from t_scm_monthlybalance where monthly_balance_month <=? and company_id=? order by monthly_balance_month desc limit 1 ", date,TokenUtil.getCompanyId(uid));
        if(last.size()>0){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
            Calendar c = Calendar.getInstance();
            c.setTime(sdf.parse(date));
            c.add(Calendar.MONTH, -1);
            String time = sdf.format(c.getTime());
            Map ll = (Map) last.get(0);
            if(time.compareTo(ll.get("monthlyBalanceMonth").toString())==0){
                if(!ll.get("monthlyBalanceStatus").equals("0")){ //0是已月结
                    throw new Exception("上月未月结！");
                }
            }else{
                throw new Exception("上月未月结!");
            }
        }

        TScmMonthlyBalance entity = new TScmMonthlyBalance();
        entity.setMonthlyBalanceMonth(date);
        //获取当前时间
        String currentTime = iCommonService.currentTime();
        //月结日期
        entity.setMonthlyBalanceDate(currentTime);
        //月结开始日期
        entity.setMonthlyBalanceStartDate(currentTime);
        entity.setCreateUser(uid);
        entity.setCreateTime(Calendar.getInstance().getTimeInMillis());
        //申请月结
        entity.setMonthlyBalanceStatus("2");
        //公司id
        entity.setCompanyId(TokenUtil.getCompanyId(uid));
        this.itScmMonthlyBalanceDao.saveOrUpdate(entity);

        //反月结时不重新生成盘点记录

        //Map nn = (Map) now.get(0);
        //if(!"1".equals(nn.get("monthlyBalanceStatus"))) {
            //生成盘点任务
            TScmInventory tScmInventory = new TScmInventory();
            tScmInventory.setTaskName(entity.getMonthlyBalanceMonth() + "盘点");
            //0未盘点 1已盘点
            tScmInventory.setTaskStatus("0");
            //0未处理 1已处理
            tScmInventory.setMakeResult("0");
            tScmInventory.setCreateUser(uid);
            tScmInventory.setCreateTime(Calendar.getInstance().getTimeInMillis());
            //公司id
            tScmInventory.setCompanyId(TokenUtil.getCompanyId(uid));
            //地点id
            tScmInventory.setLocationId(TokenUtil.getCompanyId(uid));
            if(this.itScmMonthlyBalanceDao.info("select * from t_scm_warehouse where id=? ",tScmInventory.getLocationId()).size()>0){
                tScmInventory.setWarehouseId(tScmInventory.getLocationId());
            }
            this.itScmMonthlyBalanceDao.saveOrUpdate(tScmInventory);
            //t_scm_inventory_detail表里导入数据
            if(tScmInventory.getWarehouseId()!=null&&!tScmInventory.getWarehouseId().equals("")){//仓库
                this.itScmMonthlyBalanceDao.executeSQL("insert into t_scm_inventory_detail(id,create_time,create_user,car_model,company_id,configure_name,inventory_id,vin,warehouse) " +
                        "select uuid(),t.create_time,t.create_user,t.car_model,t.company_id,t.configure_name,t.inventory_id,t.vin,t.warehouse_name from ( " +
                        "select s.vin,c.car_model car_model,s.company_id,c.configure configure_name,i.create_time,i.create_user,i.id inventory_id,w.warehouse_name " +
                        "from t_scm_stock s left join t_scm_car c on c.vin = s.vin inner join t_scm_warehouse w on s.warehouse_id = w.id,t_scm_inventory i " +
                        "where i.id=? and s.warehouse_id =?) t",tScmInventory.getId(),tScmInventory.getWarehouseId());
            }else{//公司
                this.itScmMonthlyBalanceDao.executeSQL("insert into t_scm_inventory_detail(id,create_time,create_user,car_model,company_id,configure_name,inventory_id,vin,warehouse) " +
                        "select uuid(),t.create_time,t.create_user,t.car_model,t.company_id,t.configure_name,t.inventory_id,t.vin,t.warehouse_name from ( " +
                        "select s.vin,c.car_model car_model,s.company_id,c.configure configure_name,i.create_time,i.create_user,i.id inventory_id,w.warehouse_name " +
                        "from t_scm_stock s left join t_scm_car c on c.vin = s.vin inner join t_scm_warehouse w on s.warehouse_id = w.id,t_scm_inventory i " +
                        "where i.id=? and s.company_id=?) t",tScmInventory.getId(),TokenUtil.getCompanyId(uid));
            }
        //}
    }
    //月结按钮
    @Override
    public void monthlyBalance(String uid, String date) throws Exception {

        //根据参数查询预生成月结实体
        TScmMonthlyBalance entity = (TScmMonthlyBalance) this.ihibernateDao.findEntity(TScmMonthlyBalance.class, date);

        //获取当前时间
        String currentTime = iCommonService.currentTime();

        //entity.setMonthlyBalanceMonth(date);
        //月结结束日期
        entity.setMonthlyBalanceStopDate(currentTime);
        if(!entity.getMonthlyBalanceStatus().equals("3")){
           throw new Exception("本月成本差异没通过!");
        }
        Map map = this.itScmMonthlyBalanceDao.info("select * from t_scm_inventory where task_name = ?",entity.getMonthlyBalanceMonth()+"盘点");

        if(!map.get("makeResult").equals("1")){
            throw new Exception("本月盘点未通过!");
        }
        entity.setMonthlyBalanceStatus("0");
        entity.setCreateUser(uid);
        this.itScmMonthlyBalanceDao.saveOrUpdate(entity);
        /*
        //该公司旗下的仓库
        //List<Map<String,Object>> list = this.itScmMonthlyBalanceDao.list("select id as warehouse_id from t_scm_warehouse where company_id = ?",TokenUtil.getCompanyId(uid));
        this.itScmMonthlyBalanceDao.saveOrUpdate(tScmInventory);*/
        //更新销售单月结（封账）
//        this.itScmMonthlyBalanceDao.executeSQL("update T_Scm_Sale_Order set monthly_status = 1 where" +
//                " from_unixtime(create_time/1000,'%Y%m')=DATE_FORMAT(date_add(NOW(),interval -1 MONTH),'%Y%m') ");
        //更新调拨单月结（封账）
//        this.itScmMonthlyBalanceDao.executeSQL("update T_Scm_allocation_order set monthly_status = '1' where" +
//                " from_unixtime(create_time/1000,'%Y%m')=DATE_FORMAT(date_add(NOW(),interval -1 MONTH),'%Y%m') ");
        //更新调拨出库单月结（封账）
//        this.itScmMonthlyBalanceDao.executeSQL("update T_Scm_outWarehouse set monthly_status = '1' where" +
//                " from_unixtime(create_time/1000,'%Y%m')=DATE_FORMAT(date_add(NOW(),interval -1 MONTH),'%Y%m') ");
        //更新销售出库单月结（封账）
//        this.itScmMonthlyBalanceDao.executeSQL("update T_Scm_sale_out_warehouse set monthly_status = '1' where" +
//                " from_unixtime(create_time/1000,'%Y%m')=DATE_FORMAT(date_add(NOW(),interval -1 MONTH),'%Y%m') ");
        //更新发票月结（封账）
//        this.itScmMonthlyBalanceDao.executeSQL("update T_Scm_input_invoice set monthly_status = '1' where" +
//                " from_unixtime(create_time/1000,'%Y%m')=DATE_FORMAT(date_add(NOW(),interval -1 MONTH),'%Y%m') ");
    }

    /**
     * 反月结按钮
     *
     * @param uid
     * @param id
     * @throws Exception
     */
    @Override
    public void unMonthlyBalance(String uid, String id) throws Exception {
        if (!id.isEmpty()) {
            Map map = this.itScmMonthlyBalanceDao.info("select * from "+tabName+" where id = ?",id);
            this.itScmMonthlyBalanceDao.executeSQL("update " + tabName + " set monthly_balance_status = '1',monthly_balance_date = '',monthly_balance_stop_date='',create_user ='' where" +
                    " id = ? ", id);
            List list = this.itScmMonthlyBalanceDao.list("select * from t_scm_inventory where task_name=? and company_id=?",map.get("monthlyBalanceMonth")+"盘点", TokenUtil.getCompanyId(uid));
            if(list.size()>0){
               Map m = (Map) list.get(0);
                //打开操作按钮
                this.itScmMonthlyBalanceDao.executeSQL("update t_scm_inventory set task_status = '0',make_result='0',result='' where" +
                        " id = ?",m.get("id"));
            }

        } else {
            throw new Exception("请选择月份！");
        }
    }

    //判断月结是否生成
    public void isMonthBalance(String date, String uid) throws Exception {
        if (!date.isEmpty()) {
            //先预生成月结月份
            if (this.itScmMonthlyBalanceDao.list("select distinct(monthly_balance_month) from " + tabName +
                    " where monthly_balance_month like ? ", date + "%").size() == 0) {
                for (int i = 1; i <= 12; i++) {
                    TScmMonthlyBalance tScmMonthlyBalance = new TScmMonthlyBalance();
                    tScmMonthlyBalance.setCreateTime(Calendar.getInstance().getTimeInMillis());
                    //2 默认状态
                    tScmMonthlyBalance.setMonthlyBalanceStatus("2");
                    String s = (i < 10 ? "0" : "") + String.valueOf(i);
                    tScmMonthlyBalance.setMonthlyBalanceMonth(date + s);
                    this.itScmMonthlyBalanceDao.saveOrUpdate(tScmMonthlyBalance);
                }
            }
        }
    }

    /**
     * 月结分页
     *
     * @param data
     * @param start
     * @param limit
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map manginsPage(Map data, String start, String limit, String uid) throws Exception {

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        String mDate = "";
        if (null == data.get("month") || StringUtils.isEmpty(data.get("month")) || String.valueOf(data.get("month")).length() == 0) {
            SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
            mDate = sf.format(new Date());
        } else {
            mDate = String.valueOf(data.get("month"));
        }

        boolean hasWhere = false;
        sql.append(" select * from (select tsm.id,tsm.monthly_balance_month," +
                " tsm.monthly_balance_date, tsm.monthly_balance_stop_date," +
                " tbu.u_name," +
                " case tsm.monthly_balance_status when '0' then '已月结' when '1' then '反月结' when '2' then '月结中' when '3' then '月结中'  end status, " +
                " case tsm.monthly_balance_status when '0' then ifnull(qq.diffAll,0)  else '' end diffAll " +
                " from " + tabName + " tsm left join t_bc_user tbu " +
                " on tbu.id = tsm.create_user ")
                .append("LEFT JOIN ")
                .append("( SELECT SUM(temp.diffCost) diffAll, time1 FROM ")
                .append("( SELECT tscd.new_cost - tscd.old_cost diffCost, ")
                .append("FROM_UNIXTIME(tscd.create_time/1000,'%Y%m' ) time1 ")
                .append("FROM T_SCM_COST_DIFFERENCE tscd  ")
                .append("LEFT JOIN t_scm_sale_order tsso ON tsso.vin = tscd.vin ")
                .append("LEFT JOIN t_scm_car tsc ON tsc.vin = tsso.vin ")
                .append(") AS temp GROUP BY time1 ")
                .append(") as qq on qq.time1 = tsm.monthly_balance_month ");
        String  cid = TokenUtil.getCompanyId(uid);
        if(cid!=null&&!cid.equals("")){
            sql.append("where tsm.company_id = ? ");
            params.add(cid);
        }
        //验证参数格式
        SimpleDateFormat format = new SimpleDateFormat("YYYY");
        boolean dateflag = true;
        if (data.get("year") != null && !StringUtils.isEmpty(data.get("year"))) {
            //验证参数格式
            try {
                Date d = format.parse(String.valueOf(data.get("year")));
            } catch (ParseException e) {
                dateflag = false;
            }
            sql.append(" and SUBSTR(tsm.monthly_balance_month,1,4) = ? ");
            params.add(data.get("year"));
        }
        sql.append(" order by tsm.monthly_balance_month ) as temp ");
       /* if (dateflag) {
            this.isMonthBalance(String.valueOf(data.get("year")), uid);

        } else {
            throw new Exception("时间格式不正确！");
        }*/
        return this.itScmMonthlyBalanceDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * 成本差异
     *
     * @param date 格式为201706
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map monthCostDiff(String date, String uid, String limit, String start) throws Exception {
        //验证参数格式
        SimpleDateFormat format = new SimpleDateFormat("yyyyMM");
        boolean dateflag = true;
        try {
            Date d = format.parse(date);
        } catch (ParseException e) {
            dateflag = false;
        }
        if (dateflag) {
            StringBuffer sql = new StringBuffer();
            List params = new ArrayList();
            sql.append("select * from ( select tscd.new_cost ,tscd.old_cost,round((tscd.new_cost-tscd.old_cost),2) diffCost, ")
                    .append(" tscd.vin,tsc.car_series,tsc.car_model,tsc.configure, ")
                    .append(" DATE_FORMAT(tsc.sale_date,'%Y-%m-%d') saleDate ")
                    .append("from T_SCM_COST_DIFFERENCE tscd ")
                    .append("left join t_scm_car tsc on tsc.id = tscd.car_id ")
                    .append("where FROM_UNIXTIME(tscd.create_time/1000,'%Y%m%d') like ?   ) as temp ");
            params.add(date + "%");
            return this.itScmMonthlyBalanceDao.page(sql.toString(), params.toArray(), start, limit);
        } else {
            throw new Exception("日期格式不对！");
        }
    }

    /**
     * 成本差异表通过
     * @param uid
     * @param date
     * @throws Exception
     */
    @Override
    public void confirmCostDiff(String uid,String date) throws Exception{
        //验证参数格式
        SimpleDateFormat format = new SimpleDateFormat("yyyyMM");
        boolean dateflag = true;
        Date d = new Date();
        try {
             d = format.parse(date);
        } catch (ParseException e) {
            dateflag = false;
        }
        if(dateflag){

            if(this.itScmMonthlyBalanceDao.list("select * from t_scm_monthlybalance where monthly_balance_status in ('2','1') and monthly_balance_month = ? and company_id =?",date,TokenUtil.getCompanyId(uid)).size()==0){
                throw new Exception("先申请月结!");
            }

            if(this.itScmMonthlyBalanceDao.list("select * from t_scm_monthlybalance where monthly_balance_status='3' and monthly_balance_month = ?  and company_id =?",date,TokenUtil.getCompanyId(uid)).size()>0){
               throw new Exception("该成本差异已通过!");
            }
            //差异表通过 monthly_balance_status改为3
           this.itScmMonthlyBalanceDao.executeSQL("update t_scm_monthlybalance set monthly_balance_status='3' where monthly_balance_month = ? and company_id=?",date,TokenUtil.getCompanyId(uid));

        }else {
            throw new Exception("日期格式不对！");
        }
    }

    /**
     * 财务和仓储的差异月报
     * @param start
     * @param limit
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public Map diffCar(String start,String limit,Map data,String uid) throws Exception{

        Common common = new Common();
        Boolean hasWhere = false;
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();

        sql.append("select * from ( select m.*,s.car_series_name car_series from t_scm_car_month m")
           .append(" left join t_bc_car_series s on s.id = m.Car_Series_ID");

        String cid = TokenUtil.getCompanyId(uid);
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" m.company_id = ? ");
            params.add(cid);
        }
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" m.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
        //月份
        if(data.get("dateStart")!=null&&!StringUtils.isEmpty(data.get("dateStart"))){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" m.query_date = ?");
            params.add(data.get("dateStart"));
        }

        sql .append(" order by m.query_date desc ) temp ");
        Map map = this.itScmMonthlyBalanceDao.page(sql.toString(), params.toArray(), start, limit);
        return map;
    }

    /**
     * 判断是否可以月结
     * @param companyId
     * @param date
     * @return
     * @throws Exception
     */
    @Override
    public boolean ifMonthlyBalance(String companyId,Date date) throws Exception{

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        String d = sdf.format(date);
        List list = this.itScmMonthlyBalanceDao.list("select * from t_scm_monthlybalance where monthly_balance_month =? and company_id=? ",d,companyId);
        if(list.size()==0||list==null){
            return false;
        }
        Map map = (Map) list.get(0);
        //1反月结或重置
        if(map.get("monthlyBalanceStatus").equals("1")){
            //不能月结
            return false;
        }
        //能月结
        return true;
    }

    /**
     * 月末成本差异表导出
     * @param uid
     * @param date
     * @return
     * @throws Exception
     */
    @Override
    public List<TScmDifferCost> findList(String uid, String date) throws Exception {

        List<TScmDifferCost> tScmDifferCosts = new ArrayList<>();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMM");
        boolean dateflag = true;
        try {
            Date d = format.parse(date);
        } catch (ParseException e) {
            dateflag = false;
        }
        if (dateflag) {
            StringBuffer sql = new StringBuffer();
            List params = new ArrayList();
            sql.append("select tscd.new_cost ,tscd.old_cost,round((tscd.new_cost-tscd.old_cost),2) diffCost, ")
                    .append(" tscd.vin,tsc.car_series,tsc.car_model,tsc.configure, ")
                    .append(" DATE_FORMAT(tsc.sale_date,'%Y-%m-%d') saleDate ")
                    .append("from T_SCM_COST_DIFFERENCE tscd ")
                    .append("left join t_scm_car tsc on tsc.id = tscd.car_id ")
                    .append("where FROM_UNIXTIME(tscd.create_time/1000,'%Y%m%d') like ? and tsc.company_id=?");
            params.add(date + "%");
            params.add(TokenUtil.getCompanyId(uid));
            List<Map<String, Object>> list1 = this.itScmMonthlyBalanceDao.list(sql.toString(), params.toArray());

            for (Map map : list1) {

                TScmDifferCost tt = new TScmDifferCost();
                tt.setVin(map.get("vin").toString());
                tt.setMonth(date);
                if (map.get("carSeries") != null && !map.get("carSeries").equals("")) {
                    tt.setCarSeries(map.get("carSeries").toString());
                }
                if (map.get("carModel") != null && !map.get("carModel").equals("")) {
                    tt.setCarModel(map.get("carModel").toString());
                }
                if (map.get("configure") != null && !map.get("configure").equals("")) {
                    tt.setCarCongigure(map.get("configure").toString());
                }
                if (map.get("newCost") != null && !map.get("newCost").equals("")) {
                    tt.setNewCost(map.get("newCost").toString());
                }
                if (map.get("oldCost") != null && !map.get("oldCost").equals("")) {
                    tt.setOldCost(map.get("oldCost").toString());
                }
                if (map.get("diffCost") != null && !map.get("diffCost").equals("")) {
                    tt.setDiffCost(map.get("diffCost").toString());
                }
                if (map.get("saleDate") != null && !map.get("saleDate").equals("")) {
                    tt.setSaleDate(map.get("saleDate").toString());
                }

                tScmDifferCosts.add(tt);
            }
        }
        return tScmDifferCosts;
    }

    /**
     * 月末成本差异表导出
     * @param list
     * @param uid
     * @return
     * @throws Exception
     */
    String[] excelHeader = {"月份","车系名称", "车型名称", "配置名称", "vin", "进价", "估价", "差额", "销售日期" };
    @Override
    public HSSFWorkbook exportCost(List<TScmDifferCost> list, String uid) throws Exception{
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("月末成本差异表");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeader.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmDifferCost tScmDifferCost = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmDifferCost.getMonth());
            row.createCell(1).setCellValue(tScmDifferCost.getCarSeries());
            row.createCell(2).setCellValue(tScmDifferCost.getCarModel());
            row.createCell(3).setCellValue(tScmDifferCost.getCarCongigure());
            row.createCell(4).setCellValue(tScmDifferCost.getVin());
            row.createCell(5).setCellValue(tScmDifferCost.getNewCost());
            row.createCell(6).setCellValue(tScmDifferCost.getOldCost());
            row.createCell(7).setCellValue(tScmDifferCost.getDiffCost());
            row.createCell(8).setCellValue(tScmDifferCost.getSaleDate());
        }
        return wb;
    }

    /**
     * 实物和财务对账表导出
     * @param uid
     * @param date
     * @return
     * @throws Exception
     */
    @Override
    public List<TScmDiffCar> findLists(String uid, String date) throws Exception{

        List<TScmDiffCar> tScmDiffCars = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select m.*,s.car_series_name car_series,(m.Finance_In-m.Car_In) diffIn,")
           .append("(m.Finance_Out-m.Car_Out) diffOut,(m.Finance_Stock-m.Car_Stock) diffStock")
           .append(" from t_scm_car_month m left join t_bc_car_series s on s.id = m.Car_Series_ID")
           .append(" where m.company_id=? and m.query_date = ?");
        params.add(TokenUtil.getCompanyId(uid));
        params.add(date);


        List<Map<String, Object>> list1 = this.itScmMonthlyBalanceDao.list(sql.toString(), params.toArray());

        for (Map map : list1) {

            TScmDiffCar tt = new TScmDiffCar();
            tt.setDate(date);
            if (map.get("carSeries") != null && !map.get("carSeries").equals("")) {
                tt.setCarSeries(map.get("carSeries").toString());
            }
            if (map.get("FinanceIn") != null && !map.get("FinanceIn").equals("")) {
                tt.setFinanceIn(map.get("FinanceIn").toString());
            }
            if (map.get("FinanceOut") != null && !map.get("FinanceOut").equals("")) {
                tt.setFinanceOut(map.get("FinanceOut").toString());
            }
            if (map.get("FinanceStock") != null && !map.get("FinanceStock").equals("")) {
                tt.setFinanceStock(map.get("FinanceStock").toString());
            }
            if (map.get("CarIn") != null && !map.get("CarIn").equals("")) {
                tt.setCarIn(map.get("CarIn").toString());
            }
            if (map.get("CarOut") != null && !map.get("CarOut").equals("")) {
                tt.setCarOut(map.get("CarOut").toString());
            }
            if (map.get("CarStock") != null && !map.get("CarStock").equals("")) {
                tt.setCarStock(map.get("CarStock").toString());
            }
            if (map.get("diffIn") != null && !map.get("diffIn").equals("")) {
                tt.setDiffIn(map.get("diffIn").toString());
            }
            if (map.get("diffOut") != null && !map.get("diffOut").equals("")) {
                tt.setDiffOut(map.get("diffOut").toString());
            }
            if (map.get("diffStock") != null && !map.get("diffStock").equals("")) {
                tt.setDiffStock(map.get("diffStock").toString());
            }

            tScmDiffCars.add(tt);
        }

        return tScmDiffCars;
    }

    String[] excelHeaders = {"日期", "车系名称", "财务入库", "财务出库", "财务库存", "实物入库", "实物出库", "实物库存", "差异入库", "差异出库","差异库存"};
    @Override
    public HSSFWorkbook exportDiffCar(List<TScmDiffCar> list, String uid) throws Exception {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("实物与财务差异表");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaders.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaders[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmDiffCar tScmDiffCar = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmDiffCar.getDate());
            row.createCell(1).setCellValue(tScmDiffCar.getCarSeries());
            row.createCell(2).setCellValue(tScmDiffCar.getFinanceIn());
            row.createCell(3).setCellValue(tScmDiffCar.getFinanceOut());
            row.createCell(4).setCellValue(tScmDiffCar.getFinanceStock());
            row.createCell(5).setCellValue(tScmDiffCar.getCarIn());
            row.createCell(6).setCellValue(tScmDiffCar.getCarOut());
            row.createCell(7).setCellValue(tScmDiffCar.getCarStock());
            row.createCell(8).setCellValue(tScmDiffCar.getDiffIn());
            row.createCell(9).setCellValue(tScmDiffCar.getDiffOut());
            row.createCell(10).setCellValue(tScmDiffCar.getDiffStock());

        }
        return wb;
    }

    /**
     * 判断是否未月结并没过指定日期
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public boolean ifMonthBalace(String uid) throws Exception{

        Calendar c = Calendar.getInstance();
        c.add(Calendar.MONTH, -1);
        SimpleDateFormat format =  new SimpleDateFormat("yyyyMM");
        String time = format.format(c.getTime());
        int day = c.get(Calendar.DAY_OF_MONTH);

        String cid = TokenUtil.getCompanyId(uid);

        //查看上个月的月结状态
        List list = this.itScmMonthlyBalanceDao.list("select * from t_scm_monthlybalance where company_id=? and monthly_balance_month=?",cid,time);
        if(list.size()>0){//存在上个月
            if(!"0".equals(((Map)list.get(0)).get("monthlyBalance"))){//未月结
                //查看是否过了指定日期
               Map map = this.itScmMonthlyBalanceDao.info("select * from t_bc_param_set where code = 'endMonthBalance' and company_id='0' and p_code='scm'");
               int num = Integer.parseInt(map.get("value").toString());
               if(day<=num){
                   return true;
               }
            }
        }
        return false;
    }
}
